var express = require('express');
var fs = require("fs");
var router = express.Router();
var PATH = "./public/data/";
var dayjs = require('dayjs');
var multiparty = require('multiparty');
const mysql = require('mysql2');
const { log } = require('console');
const e = require('express');

// 创建连接
const connection = mysql.createConnection({
  host: '192.168.1.64',
  user: 'lpw94',
  password: '111111',
  database: 'test',
  port: 3306,
});




var destpath = "./public/img/";


router.get('/info/:type', function (req, res, next) {
  var pageSize = Number(req.param('pageSize')) || 10;
  var pageIndex = Number(req.param('pageIndex')) || 1;
  var type = req.param('type') || '';

  if(type == 'news'){
    fs.readFile(PATH + type + ".json", function (err, data) {
      //读取文件出现异常
      if (err) {
        //接口返回数据
        return res.send({
          status: 0,
          info: "读取文件异常",
          data: []
        })
      }
      //控制读取数据的数量
      // var totalPages = Math.ceil(JSON.parse(data.toString()).length / pageSize);
      var result = [];
      //捕获异常，防止文件读取异常
      try {
        result = JSON.parse(data.toString());
      } catch (e) {
        result = [];
      }
  
      //1) 总条数小于等于每页数 那就只有一页
      if (result.length <= pageSize) {
        totalPages = 1;
        result = result;
      } else {
        //2) 总条数大于每页数
        totalPages = result.length % pageSize == 0 ? result.length / pageSize : Math.ceil(result.length / pageSize);
        //3）处理数据进行分页操作 0,10  10,20  20,30  30,40  40,50
        result = result.slice((pageIndex - 1) * pageSize, pageSize * pageIndex);
      }
  
      //接口返回数据
      return res.send({
        code: 200,
        info: "读取文件成功",
        data: result,
        total: data ? JSON.parse(data.toString()).length : 0,
        totalPages: totalPages,
        pageSize: pageSize,
        pageIndex: pageIndex
      })
    });
  }else{
    var sqltotal = 'SELECT COUNT(*) FROM userinfo;';
        var sql = `SELECT * FROM userinfo limit ${(pageIndex-1) * pageSize}, ${pageSize}`;
        log(pageSize, pageIndex, '===========');
    
        // 创建连接
      const connection = mysql.createConnection({
        host: '192.168.1.64',
        user: 'lpw94',
        password: '111111',
        database: 'test',
        port: 3306,
      });
    
    
    
        //查
        connection.query(sql,function (err, result) {
                if(err){
                  console.log('[SELECT ERROR] - ',err.message);
                  return;
                }
        
              console.log('--------------------------SELECT----------------------------', pageSize);
              console.log(result);
              console.log('------------------------------------------------------------\n\n'); 
              
              var list = result.map((item)=>{
                item.time = dayjs(item.time).format('YYYY-MM-DD HH:mm:ss');
                return item
              })
              connection.query(sqltotal, (err, results2) => {
                if (err) throw err;
                console.log('Results for query 2:', results2);
                connection.end();
                //接口返回数据
                return res.send({
                  code: 200,
                  info: "读取文件成功",
                  data: result,
                  total: 0,
                  totalPages: results2[0]['COUNT(*)']%pageSize == 0 ? results2[0]['COUNT(*)'] / pageSize : Math.ceil(results2[0]['COUNT(*)'] / pageSize), //总页数
                  pageSize: pageSize,
                  pageIndex: pageIndex
                })
    
                
              });
        });
    
  }
  
});






/* GET about page. */
router.post('/submit', function (req, res, next) {
  //关键字段
  var fileType = 'news';
  //关键字段
  var type = req.query.type || '';
  var filePath = PATH + fileType + '.json';
  if (type === 'file') {
    var form = new multiparty.Form();
    form.encoding = 'utf-8';
    form.uploadDir = destpath;
    form.maxFilesSize = 20 * 1024 * 1024;
    form.parse(req, (err, fields, files) => {
      if (err) {
        console.log('解析上传文件错误:', err);
      } else {
        var originalFilename = files.file[0].originalFilename;
        var name = fields.name[0]
        var dept = fields.dept[0]
        var content = fields.content[0]

        var fileType = findFileType(originalFilename);
        // var fileName = originalFilename;
        var uploadedPath = files.file[0].path;
        var newFileName = destpath + formatDate(new Date()) + fileType;

        fs.rename(uploadedPath, newFileName, (err) => {
          if (err) {
            //   console.log('重命名文件错误:', err);
            return res.send({
              code: 0,
              info: '重命名文件错误',
              data: err
            });
          }
          fs.readFile(filePath, function (err, data) {
            if (err) {
              return res.send({
                code: 0,
                info: '读取数据失败',
                data: []
              });
            }

            var arr = data.toString() ? JSON.parse(data.toString()) : [];
            //代表每一条记录
            var obj = {
              name: name,
              dept: dept,
              content: content,
              id: new Date().getTime(),
              src: originalFilename ? newFileName.substring(8) : ('/img/img' + Math.floor(Math.random() * 11) + '.jpg'),
              time: dayjs(new Date()).format('YYYY-MM-DD HH:mm:ss')
            };
            //将记录写在文件最前面
            arr.splice(0, 0, obj);
            //2)写入文件
            var newData = JSON.stringify(arr);
            fs.writeFile(filePath, newData, function (err) {
              if (err) {
                return res.send({
                  code: 0,
                  info: '写入文件失败',
                  data: []
                });
                // res.redirect('/help');
              }

              // 连接数据库
      connection.connect(err => {
        if (err) {
          return console.error('error: ' + err.message);
        }

        console.log('Connected to the MySQL server.');


        var addSql = 'INSERT INTO usersubmit(name,dept,content,src,id,time) VALUES(?,?,?,?,?,?)';
        var addSqlParams = [obj.name, obj.dept, obj.content, obj.src, obj.id, obj.time]; 
        //增
        connection.query(addSql, addSqlParams, function (err, result) {
          if (err) {
            console.log('[INSERT ERROR] - ', err.message);
            return res.send({
              code: 0,
              info: '写入数据库失败',
              data: []
            });
          }

          console.log('--------------------------INSERT----------------------------');
          //console.log('INSERT ID:',result.insertId);        
          console.log('INSERT ID:', result);
          console.log('-----------------------------------------------------------------\n\n');
          return res.send({
            code: 200,
            info: '写入文件+ 数据库成功',
            data: obj
          });
        });
      });

              // return res.send({
              //   code: 200,
              //   info: '写入文件成功',
              //   data: obj
              // });
              // res.redirect('/help');
            });
          });
        });


      }
    });
  } else {

    var name = req.param('name') || '';
    var dept = req.param('dept') || '';
    var content = req.param('content') || '';
    console.log(name, '666')
    fs.readFile(filePath, function (err, data) {
      if (err) {
        return res.send({
          code: 0,
          info: '读取数据失败',
          data: []
        });
      }

      var arr = data.toString() ? JSON.parse(data.toString()) : [];
      //代表每一条记录
      var obj = {
        name: name,
        dept: dept,
        content: content,
        src: '/img/img' + Math.floor(Math.random() * 11) + '.jpg',
        id: new Date().getTime(),
        time: dayjs(new Date()).format('YYYY-MM-DD HH:mm:ss')
      };
      //将记录写在文件最前面
      arr.splice(0, 0, obj);
      //2)写入文件
      var newData = JSON.stringify(arr);
      fs.writeFile(filePath, newData, function (err) {
        if (err) {
          return res.send({
            code: 0,
            info: '写入文件失败',
            data: []
          });
        }

        // 连接数据库
      connection.connect(err => {
        if (err) {
          return console.error('error: ' + err.message);
        }

        console.log('Connected to the MySQL server.');


        var addSql = 'INSERT INTO usersubmit(name,dept,content,src,id,time) VALUES(?,?,?,?,?,?)';
        var addSqlParams = [obj.name, obj.dept, obj.content, obj.src, obj.id, obj.time]; 
        //增
        connection.query(addSql, addSqlParams, function (err, result) {
          if (err) {
            console.log('[INSERT ERROR] - ', err.message);
            return res.send({
              code: 0,
              info: '写入数据库失败',
              data: []
            });
          }

          console.log('--------------------------INSERT----------------------------');
          //console.log('INSERT ID:',result.insertId);        
          console.log('INSERT ID:', result);
          console.log('-----------------------------------------------------------------\n\n');
          return res.send({
            code: 200,
            info: '写入文件+ 数据库成功',
            data: obj
          });
        });
      });

        // return res.send({
        //   code: 200,
        //   info: '写入文件成功',
        //   data: obj
        // });
      });
    });
  }

});

/* GET about page. */
router.post('/save', function (req, res, next) {


  //关键字段
  var fileType = 'news';
  var type = req.query.type || '';
  var filePath = PATH + fileType + '.json';

  var name = req.param('name') || '';
  var dept = req.param('dept') || '';
  var content = req.param('content') || '';
  fs.readFile(filePath, function (err, data) {
    if (err) {
      return res.send({
        code: 0,
        info: '读取数据失败',
        data: []
      });
    }

    var arr = data.toString() ? JSON.parse(data.toString()) : [];
    //代表每一条记录
    var obj = {
      name: name,
      dept: dept,
      content: content,
      src: '/img/img' + Math.floor(Math.random() * 11) + '.jpg',
      id: new Date().getTime(),
      time: dayjs(new Date()).format('YYYY-MM-DD HH:mm:ss')
    };
    //将记录写在文件最前面
    arr.splice(0, 0, obj);
    //2)写入文件
    var newData = JSON.stringify(arr);
    fs.writeFile(filePath, newData, function (err) {
      if (err) {
        return res.send({
          code: 0,
          info: '写入文件失败',
          data: []
        });
      }
      // 连接数据库
      connection.connect(err => {
        if (err) {
          return console.error('error: ' + err.message);
        }

        console.log('Connected to the MySQL server.');


        var addSql = 'INSERT INTO usersubmit(name,dept,content,src,id,time) VALUES(?,?,?,?,?,?)';
        var addSqlParams = [obj.name, obj.dept, obj.content, obj.src, obj.id, obj.time]; 
        //增
        connection.query(addSql, addSqlParams, function (err, result) {
          if (err) {
            console.log('[INSERT ERROR] - ', err.message);
            return res.send({
              code: 0,
              info: '写入数据库失败',
              data: []
            });
          }

          console.log('--------------------------INSERT----------------------------');
          //console.log('INSERT ID:',result.insertId);        
          console.log('INSERT ID:', result);
          console.log('-----------------------------------------------------------------\n\n');
          return res.send({
            code: 200,
            info: '写入文件+ 数据库成功',
            data: obj
          });
        });
      });
    });
  });

});











function formatDate(date) {
  if (date == null) {
    return "";
  }
  return dayjs(date).format('YYYYMMDD-HHmmss')
}
function findFileType(originalFilename) {
  var lastIndex = originalFilename.lastIndexOf('.');
  if (lastIndex != -1) {
    return originalFilename.substring(lastIndex);
  }
  return originalFilename;
}

module.exports = router;
